<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Lab extends MY_Controller {

    public function __construct() {
        parent::__construct();
        if (!($this->_loggedUser != NULL && $this->_loggedUser->id == 0)) {
            redirect("home/error");
        } else {
            echo "<style type='text/css'>* { background-color: black; font-family: courier; color: white; font-size: x-small; }</style>";
        }
    }

    public function setadminpass($pass) {
        $this->load->database("homepage");
        $this->db->update("user", Array("password" => $this->encrypt->encode($pass)), Array("id" => 0));
    }

    public function showadminpass() {
        $this->load->database("homepage");
        $res = $this->db->get_where("user", Array("id" => 0))->row();
        if ($res) {
            $res->password = $this->encrypt->decode($res->password);
        }
        var_dump($res);
    }

    public function showstats($site = "homepage") {
        $layar = "";

        $today = getdate();
        $weekStartDate = $today['mday'] - $today['wday'];
        $weekEndDate = $today['mday'] - $today['wday'] + 6;
        $mingguIni = date('Y-m') . "-" . ($weekStartDate < 10 ? "0" : "") . $weekStartDate . " 00:00:00";
        $sabtuIni = date('Y-m') . "-" . ($weekEndDate < 10 ? "0" : "") . $weekEndDate . " 23:59:59";
        $layar .= $mingguIni . " s/d " . $sabtuIni . "<hr />";

        switch ($site) {
            case "homepage" :
                $this->load->database("homepage");

                // data relawan
                $layar .= "<br />";
                $sql = "SELECT user.id FROM user, `group`, user_group WHERE user.id=user_group.user_id AND `group`.id=user_group.group_id AND `group`.name LIKE 'RELAWAN 2014 REGION%'";
                $query = $this->db->query($sql);
                $layar .= "Total relawan terdaftar : " . count($query->result()) . "<br />";
                $sql = "SELECT user.id FROM user, `group`, user_group WHERE user.id=user_group.user_id AND `group`.id=user_group.group_id AND `group`.name LIKE 'RELAWAN 2014 REGION%' AND user.password NOT LIKE '8hIwFt7ao/wdvPu8dFBcsGFTuPsZIiChdwYVn0TpLp6MVT2tE4z3WAurgTN0maDCYPpB914GfIQK/Q1jw42Aow=='";
                $query = $this->db->query($sql);
                $layar .= "Total relawan ganti password : " . count($query->result()) . "<br />";

                // per region
                $regRom = Array(
                    "I", "II", "III", "IV", "V", "VI", "VII"
                );
                for ($i = 0; $i < 7; $i++) {
                    $sql = "SELECT user.id FROM user, `group`, user_group WHERE user.id=user_group.user_id AND `group`.id=user_group.group_id AND `group`.name LIKE 'RELAWAN 2014 REGION " . $regRom[$i] . "' AND user.password NOT LIKE '8hIwFt7ao/wdvPu8dFBcsGFTuPsZIiChdwYVn0TpLp6MVT2tE4z3WAurgTN0maDCYPpB914GfIQK/Q1jw42Aow=='";
                    $query = $this->db->query($sql);
                    $layar .= "Region " . $regRom[$i] . " : " . count($query->result());
                    $sql = "SELECT user.id FROM user, `group`, user_group WHERE user.id=user_group.user_id AND `group`.id=user_group.group_id AND `group`.name LIKE 'RELAWAN 2014 REGION " . $regRom[$i] . "'";
                    $query = $this->db->query($sql);
                    $layar .= " / " . count($query->result()) . "<br />";
                }

                // kabar relawan
                $layar .= "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='KR' AND content.posted BETWEEN '" . $mingguIni . "' AND '" . $sabtuIni . "'";
                $query = $this->db->query($sql);
                $layar .= "Total kabar relawan diposting : " . count($query->result()) . "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='KR' AND content.posted BETWEEN '" . $mingguIni . "' AND '" . $sabtuIni . "'";
                $sql .= " AND article.is_deleted=0 AND article.is_approved=1";
                $query = $this->db->query($sql);
                $layar .= "Total kabar relawan dipublish : " . count($query->result()) . "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='KR'";
                $sql .= " AND article.is_deleted=0 AND article.is_approved=1";
                $query = $this->db->query($sql);
                $layar .= "Total kabar relawan dipublish (all-time) : " . count($query->result()) . "<br />";

                // berita ps
                $layar .= "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='B' AND content.posted BETWEEN '" . $mingguIni . "' AND '" . $sabtuIni . "'";
                $query = $this->db->query($sql);
                $layar .= "Total berita diposting : " . count($query->result()) . "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='B' AND content.posted BETWEEN '" . $mingguIni . "' AND '" . $sabtuIni . "'";
                $sql .= " AND article.is_deleted=0 AND article.is_approved=1";
                $query = $this->db->query($sql);
                $layar .= "Total berita dipublish : " . count($query->result()) . "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='B'";
                $sql .= " AND article.is_deleted=0 AND article.is_approved=1";
                $query = $this->db->query($sql);
                $layar .= "Total berita dipublish (all-time) : " . count($query->result()) . "<br />";

                // jurnalisme publik
                $layar .= "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='PJ' AND content.posted BETWEEN '" . $mingguIni . "' AND '" . $sabtuIni . "'";
                $query = $this->db->query($sql);
                $layar .= "Total artikel publik diposting : " . count($query->result()) . "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='PJ' AND content.posted BETWEEN '" . $mingguIni . "' AND '" . $sabtuIni . "'";
                $sql .= " AND article.is_deleted=0 AND article.is_approved=1";
                $query = $this->db->query($sql);
                $layar .= "Total artikel publik dipublish : " . count($query->result()) . "<br />";
                $sql = "SELECT * FROM article, content WHERE article.content_id=content.id AND article.type='PJ'";
                $sql .= " AND article.is_deleted=0 AND article.is_approved=1";
                $query = $this->db->query($sql);
                $layar .= "Total artikel publik dipublish (all-time) : " . count($query->result()) . "<br />";
                break;
            case "data" :
                // data
                $this->load->database("data_ps");

                $layar .= "<br />";
                $sqldata = "SELECT count(id) as count FROM data WHERE is_deleted = 0";
                $querydata = $this->db->query($sqldata);
                $layar .= "Total data : " . $querydata->row()->count . "<br />";

                $layar .= "<br />";
                $sqldata = "SELECT count(id) as count FROM data, region_data WHERE region_data.data_id = data.id AND data.is_deleted = 0";
                $querydata = $this->db->query($sqldata);
                $layar .= "Tingkat Regional : " . $querydata->row()->count . "<br />";

                $layar .= "<br />";
                $sqldata = "SELECT count(id) as count FROM data, provinsi_data WHERE provinsi_data.data_id = data.id AND data.is_deleted = 0";
                $querydata = $this->db->query($sqldata);
                $layar .= "Tingkat Provinsi : " . $querydata->row()->count . "<br />";

                $layar .= "<br />";
                $sqldata = "SELECT count(id) as count FROM data, kabupaten_data WHERE kabupaten_data.data_id = data.id AND data.is_deleted = 0";
                $querydata = $this->db->query($sqldata);
                $layar .= "Tingkat Kabupaten : " . $querydata->row()->count . "<br />";

                $layar .= "<br />";
                $sqldata = "SELECT count(id) as count FROM data, kecamatan_data WHERE kecamatan_data.data_id = data.id AND data.is_deleted = 0";
                $querydata = $this->db->query($sqldata);
                $layar .= "Tingkat Kecamatan : " . $querydata->row()->count . "<br />";

                $layar .= "<br />";
                $sqldata = "SELECT count(id) as count FROM data, perdesaan_data WHERE perdesaan_data.data_id = data.id AND data.is_deleted = 0";
                $querydata = $this->db->query($sqldata);
                $layar .= "Tingkat Desa : " . $querydata->row()->count . "<br />";

                $layar .= "<br />";
                $sqldata = "SELECT count(id) as count FROM data, puskesmas_data WHERE puskesmas_data.data_id = data.id AND data.is_deleted = 0";
                $querydata = $this->db->query($sqldata);
                $layar .= "Tingkat Puskesmas : " . $querydata->row()->count . "<br />";

                $layar .= "<br />";
                $sqldata = "SELECT source.name, data.year FROM data, source WHERE data.source_id = source.id GROUP BY source.name, data.year";
                $querydata = $this->db->query($sqldata);
                $layar .= "Sumber Data : <br />";
                $layar .= "<ul>";
                foreach ($querydata->result() as $row) {
                    $layar .= "<li>" . $row->name . " " . $row->year . "</li>";
                }
                $layar .= "</li>";

                break;
        }

        echo $layar;
    }

    public function parsegroupmember() {
        echo "<html><head></head><body><form action='" . site_url("lab/parsegroupmember") . "' method='POST'>";

        echo "<select name='idg'>";
        $this->load->model("Group_model");
        foreach ($this->Group_model->findAll() as $group) {
            echo "<option value='" . $group->id . "'>" . $group->name . "</option>";
        }
        echo "</select>";

        echo "<textarea name='l'></textarea>";

        echo "<input type='submit' />";
        echo "</form><hr /></body></html>";

        $idGroup = $this->input->post("idg");
        $list = $this->input->post("l");

        $this->load->model("Group_model");
        if ($this->Group_model->findById($idGroup) != NULL) {
            $rows = explode("\n", $list);
            $uids = Array();
            foreach ($rows as $row) {
                $split = explode(";", $row);
                if (count($split) == 2) {
                    $email = $split[1];
                    $name = $split[0];
                    $this->load->database("homepage");
                    $this->db->from("user");
                    $this->db->where("REPLACE(LOWER(email), ' ', '') LIKE REPLACE(LOWER('" . trim($email) . "'), ' ', '')");
                    $query = $this->db->get();
                    $results = $query->result();
                    if (count($results) < 1) {
                        // insert new
                        $this->db->insert('user', Array(
                            "email" => strtolower(str_replace(" ", "", trim($email))),
                            "display_name" => $name,
                            "register_date" => "2014-03-21",
                            "password" => "8hIwFt7ao/wdvPu8dFBcsGFTuPsZIiChdwYVn0TpLp6MVT2tE4z3WAurgTN0maDCYPpB914GfIQK/Q1jw42Aow=="
                        ));
                        $uId = $this->db->insert_id();
                        $this->db->insert('user_group', Array(
                            "user_id" => $uId,
                            "group_id" => $idGroup
                        ));
                        array_push($uids, $uId);
                        echo "Generated : " . $name . " " . $email . " " . $uId . "<br />";
                    } else {
                        foreach ($results as $res) {
                            $this->db->where('id', $res->id);
                            $this->db->update('user', Array(
                                "email" => strtolower(str_replace(" ", "", trim($email))),
                                "display_name" => $name,
                            ));
                            $this->db->delete('user_group', array('user_id' => $res->id));
                            $this->db->insert('user_group', Array(
                                "user_id" => $res->id,
                                "group_id" => $idGroup
                            ));
                            array_push($uids, $res->id);
                            echo "Updated : " . $name . " " . $email . " " . $res->id . "<br />";
                        }
                    }
                }
            }
            $userids = "";
            foreach ($uids as $user_id) {
                if ($userids != "") {
                    $userids .= ",";
                }
                $userids .= $user_id;
            }
            $this->db->delete('user_group', 'group_id = ' . $idGroup . ' AND user_id NOT IN (' . $userids . ')');
        }
    }

    public function parsedataindikator() {
        $this->load->helper("form");
        echo form_open_multipart("lab/parsedataindikator");
        echo 'Meta : <input type="file" name="metafile" size="20" />';
        echo '<br />';
        echo 'Data : <input type="file" name="datafile" size="20" />';
        echo '<br /><br />';
        echo '<input type="hidden" name="uploading" value="1" />';
        echo '<input type="submit" value="upload" />';
        echo "</form>";
        //.................................................
        if ($this->input->post("uploading") == "1") {
            $fileConf = Array(
                "allowed_types" => "txt",
                "upload_path" => APPPATH . "temp/",
                "file_name" => "meta.txt",
                "overwrite" => true
            );
            $this->load->library('upload', $fileConf);
            if ($this->upload->do_upload("metafile")) {
                $fileConf = Array(
                    "allowed_types" => "txt",
                    "upload_path" => APPPATH . "temp/",
                    "file_name" => "data.txt",
                    "overwrite" => true
                );
                $this->upload->initialize($fileConf);
                if ($this->upload->do_upload("datafile")) {
                    $metaText = read_file(APPPATH . "temp/meta.txt");
                    $dataText = read_file(APPPATH . "temp/data.txt");
                    $this->load->model("De_form_model");
                    $this->De_form_model->parseUpdate(0, $metaText, $dataText);
                    unlink(APPPATH . "temp/data.txt");
                } else {
                    echo $this->upload->display_errors();
                }
                unlink(APPPATH . "temp/meta.txt");
            } else {
                echo $this->upload->display_errors();
            }
        }
    }

    public function getstatbansos() {
        $this->load->helper("form");
        echo form_open_multipart("lab/getstatbansos");
        echo 'Meta : <input type="file" name="metafile" size="20" />';
        echo '<br /><br />';
        echo '<input type="hidden" name="uploading" value="1" />';
        echo '<input type="submit" value="upload" />';
        echo "</form>";
        //.................................................
        if ($this->input->post("uploading") == "1") {
            $fileConf = Array(
                "allowed_types" => "txt",
                "upload_path" => APPPATH . "temp/",
                "file_name" => "daftarkab.txt",
                "overwrite" => true
            );
            $this->load->library('upload', $fileConf);
            if ($this->upload->do_upload("metafile")) {
                $metaText = read_file(APPPATH . "temp/daftarkab.txt");

                echo "<table>";
                echo "<thead>";
                echo "<tr>";
                echo "<th>" . "Kabupaten" . "</th>";
                echo "<th>" . "RSUD" . "</th>";
                echo "<th>" . "Puskesmas" . "</th>";
                echo "<th>" . "Poskesdes" . "</th>";
                echo "<th>" . "Desa" . "</th>";
                echo "</tr>";
                echo "</thead>";
                echo "<tbody>";
                foreach (explode("\n", $metaText) as $row) {
                    echo "<tr>";
                    $this->load->model("Kabupaten_model");
                    $kabObj = $this->Kabupaten_model->findByName(trim($row));
                    if ($kabObj != NULL) {
                        echo "<td>" . $kabObj->display_name . "</td>";
                        $this->load->model("De_data_model");
                        $rsuddata = $this->De_data_model->getByLocInd("KAB", $kabObj->code, 294);
                        if (count($rsuddata) > 0) {
                            echo "<td>" . $rsuddata[0]->value . "</td>";
                        }
                        $puskesmasdata = $this->De_data_model->getByLocInd("KAB", $kabObj->code, 88);
                        if (count($puskesmasdata) > 0) {
                            echo "<td>" . $puskesmasdata[0]->value . "</td>";
                        }
                        $poskesdesdata = $this->De_data_model->getByLocInd("KAB", $kabObj->code, 115);
                        if (count($poskesdesdata) > 0) {
                            echo "<td>" . $poskesdesdata[0]->value . "</td>";
                        }
                        $desadata = $this->De_data_model->getByLocInd("KAB", $kabObj->code, 43);
                        if (count($desadata) > 0) {
                            echo "<td>" . $desadata[0]->value . "</td>";
                        }
                    } else {
                        echo "<td colspan='5'>" . $row . "</td>";
                    }
                    echo "</tr>";
                }
                echo "</tbody>";
                echo "</table>";

                unlink(APPPATH . "temp/daftarkab.txt");
            } else {
                echo $this->upload->display_errors();
            }
        }
    }

}